{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "14a02712",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:31.777404Z",
     "start_time": "2022-01-05T07:53:30.676028Z"
    }
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import datetime\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "121a3f95",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:31.824351Z",
     "start_time": "2022-01-05T07:53:31.780403Z"
    }
   },
   "outputs": [],
   "source": [
    "# 数据库地址：数据库放在上一级目录下\n",
    "db_path = os.path.join(os.path.dirname(os.getcwd()), \"data.db\")\n",
    "engine_path = \"sqlite:///\" + db_path\n",
    "# 创建数据库引擎\n",
    "engine = create_engine(engine_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbcd24f9",
   "metadata": {},
   "source": [
    "### 获取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "faa12069",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:32.314051Z",
     "start_time": "2022-01-05T07:53:31.827278Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>user_id</th>\n",
       "      <th>create_time</th>\n",
       "      <th>order_id</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13166</th>\n",
       "      <td>13166</td>\n",
       "      <td>72797</td>\n",
       "      <td>2021/2/26</td>\n",
       "      <td>4137903132</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62145</th>\n",
       "      <td>62145</td>\n",
       "      <td>104941</td>\n",
       "      <td>2021/10/19</td>\n",
       "      <td>4201557665</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29403</th>\n",
       "      <td>29403</td>\n",
       "      <td>127175</td>\n",
       "      <td>2021/5/11</td>\n",
       "      <td>4192533398</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67264</th>\n",
       "      <td>67264</td>\n",
       "      <td>147490</td>\n",
       "      <td>2021/11/15</td>\n",
       "      <td>4201144712</td>\n",
       "      <td>690.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62477</th>\n",
       "      <td>62477</td>\n",
       "      <td>142531</td>\n",
       "      <td>2021/10/21</td>\n",
       "      <td>4202724341</td>\n",
       "      <td>3999.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       index  user_id create_time    order_id  amount\n",
       "13166  13166    72797   2021/2/26  4137903132    59.0\n",
       "62145  62145   104941  2021/10/19  4201557665    49.0\n",
       "29403  29403   127175   2021/5/11  4192533398    59.0\n",
       "67264  67264   147490  2021/11/15  4201144712   690.0\n",
       "62477  62477   142531  2021/10/21  4202724341  3999.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "select * from business\n",
    "\"\"\"\n",
    "\n",
    "df = pd.read_sql(sql,engine)\n",
    "\n",
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8ae3bc4a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:32.360852Z",
     "start_time": "2022-01-05T07:53:32.318965Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 76050 entries, 0 to 76049\n",
      "Data columns (total 5 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   index        76050 non-null  int64  \n",
      " 1   user_id      76050 non-null  int64  \n",
      " 2   create_time  76048 non-null  object \n",
      " 3   order_id     76050 non-null  int64  \n",
      " 4   amount       76043 non-null  float64\n",
      "dtypes: float64(1), int64(3), object(1)\n",
      "memory usage: 2.9+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c1954360",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:32.439640Z",
     "start_time": "2022-01-05T07:53:32.365840Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 76041 entries, 0 to 76049\n",
      "Data columns (total 5 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   index        76041 non-null  int64  \n",
      " 1   user_id      76041 non-null  int64  \n",
      " 2   create_time  76041 non-null  object \n",
      " 3   order_id     76041 non-null  int64  \n",
      " 4   amount       76041 non-null  float64\n",
      "dtypes: float64(1), int64(3), object(1)\n",
      "memory usage: 3.5+ MB\n"
     ]
    }
   ],
   "source": [
    "# create_time和amount有缺失值，去掉缺失值\n",
    "df2 = df.copy()\n",
    "\n",
    "# dropna() 默认只要该行有 nan 值就删除\n",
    "df2 = df2.dropna()\n",
    "\n",
    "df2.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55b86657",
   "metadata": {},
   "source": [
    "### 数据分析"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a33a6b1a",
   "metadata": {},
   "source": [
    "#### Recent【最近一次消费数据】"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5584fff0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:32.471595Z",
     "start_time": "2022-01-05T07:53:32.442637Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>user_id</th>\n",
       "      <th>create_time</th>\n",
       "      <th>order_id</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9768</th>\n",
       "      <td>9768</td>\n",
       "      <td>105374</td>\n",
       "      <td>2021/2/12</td>\n",
       "      <td>4177624931</td>\n",
       "      <td>299.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      index  user_id create_time    order_id  amount\n",
       "9768   9768   105374   2021/2/12  4177624931   299.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "30652388",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:32.503470Z",
     "start_time": "2022-01-05T07:53:32.477539Z"
    }
   },
   "outputs": [],
   "source": [
    "now_ = pd.to_datetime(datetime.datetime.now())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c423e07e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.383720Z",
     "start_time": "2022-01-05T07:53:32.508456Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>user_id</th>\n",
       "      <th>create_time</th>\n",
       "      <th>order_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>recent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>50572</th>\n",
       "      <td>50572</td>\n",
       "      <td>85526</td>\n",
       "      <td>2021/8/22</td>\n",
       "      <td>4198432890</td>\n",
       "      <td>2499.0</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52019</th>\n",
       "      <td>52019</td>\n",
       "      <td>155789</td>\n",
       "      <td>2021/8/29</td>\n",
       "      <td>4194429805</td>\n",
       "      <td>1699.0</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34287</th>\n",
       "      <td>34287</td>\n",
       "      <td>147180</td>\n",
       "      <td>2021/6/4</td>\n",
       "      <td>4176346242</td>\n",
       "      <td>11.9</td>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10736</th>\n",
       "      <td>10736</td>\n",
       "      <td>148400</td>\n",
       "      <td>2021/2/16</td>\n",
       "      <td>4190923658</td>\n",
       "      <td>399.0</td>\n",
       "      <td>323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68004</th>\n",
       "      <td>68004</td>\n",
       "      <td>87578</td>\n",
       "      <td>2021/11/18</td>\n",
       "      <td>4194488169</td>\n",
       "      <td>9.0</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       index  user_id create_time    order_id  amount  recent\n",
       "50572  50572    85526   2021/8/22  4198432890  2499.0     136\n",
       "52019  52019   155789   2021/8/29  4194429805  1699.0     129\n",
       "34287  34287   147180    2021/6/4  4176346242    11.9     215\n",
       "10736  10736   148400   2021/2/16  4190923658   399.0     323\n",
       "68004  68004    87578  2021/11/18  4194488169     9.0      48"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 添加时间差数据\n",
    "df2[\"recent\"] = df2[\"create_time\"].map(lambda x:(now_-pd.to_datetime(x)).days)\n",
    "\n",
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "847b464b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.414638Z",
     "start_time": "2022-01-05T07:53:41.387709Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "55540"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df2.user_id.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "71832c66",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.461549Z",
     "start_time": "2022-01-05T07:53:41.419622Z"
    }
   },
   "outputs": [],
   "source": [
    "# 用户最近一次购买商品的时间\n",
    "recent_df = df2.groupby(by=\"user_id\",as_index=False).agg({\"recent\":\"min\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "21715fa8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.493428Z",
     "start_time": "2022-01-05T07:53:41.465501Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>recent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>49443</th>\n",
       "      <td>152385</td>\n",
       "      <td>214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21621</th>\n",
       "      <td>95355</td>\n",
       "      <td>232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36916</th>\n",
       "      <td>126707</td>\n",
       "      <td>139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4576</th>\n",
       "      <td>60465</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34558</th>\n",
       "      <td>121836</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       user_id  recent\n",
       "49443   152385     214\n",
       "21621    95355     232\n",
       "36916   126707     139\n",
       "4576     60465      26\n",
       "34558   121836      70"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recent_df.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bace14a",
   "metadata": {},
   "source": [
    "#### Frequency【一段时间内的消费次数】"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "41063e39",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.557254Z",
     "start_time": "2022-01-05T07:53:41.499411Z"
    }
   },
   "outputs": [],
   "source": [
    "frequency_df = df2.groupby(by=\"user_id\",as_index=False).agg({\"order_id\":\"count\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "976c899f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.589170Z",
     "start_time": "2022-01-05T07:53:41.561244Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11421</th>\n",
       "      <td>74270</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41587</th>\n",
       "      <td>136108</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15319</th>\n",
       "      <td>82389</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21054</th>\n",
       "      <td>94248</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34042</th>\n",
       "      <td>120846</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       user_id  order_id\n",
       "11421    74270        12\n",
       "41587   136108         6\n",
       "15319    82389         6\n",
       "21054    94248         6\n",
       "34042   120846         6"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frequency_df.sort_values(by=\"order_id\",ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7558d6eb",
   "metadata": {},
   "source": [
    "#### Mount【一段时间内的消费总金额】"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "308bc08d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.636044Z",
     "start_time": "2022-01-05T07:53:41.593163Z"
    }
   },
   "outputs": [],
   "source": [
    "mount_df = df2.groupby(by=\"user_id\",as_index=False).agg({\"amount\":\"sum\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "fe8b7921",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.666961Z",
     "start_time": "2022-01-05T07:53:41.640033Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11421</th>\n",
       "      <td>74270</td>\n",
       "      <td>89566.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13524</th>\n",
       "      <td>78669</td>\n",
       "      <td>31010.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51252</th>\n",
       "      <td>156095</td>\n",
       "      <td>18957.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5773</th>\n",
       "      <td>62830</td>\n",
       "      <td>18164.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41884</th>\n",
       "      <td>136682</td>\n",
       "      <td>17749.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       user_id   amount\n",
       "11421    74270  89566.9\n",
       "13524    78669  31010.9\n",
       "51252   156095  18957.0\n",
       "5773     62830  18164.0\n",
       "41884   136682  17749.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mount_df.sort_values(by=\"amount\",ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfe5d551",
   "metadata": {},
   "source": [
    "### RFM模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "de978fb4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.760711Z",
     "start_time": "2022-01-05T07:53:41.671948Z"
    }
   },
   "outputs": [],
   "source": [
    "# 根据 user_id 合并数据\n",
    "rfm_df = recent_df.merge(\n",
    "    frequency_df,on=\"user_id\",how=\"left\"\n",
    ").merge(\n",
    "    mount_df,on=\"user_id\",how=\"left\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "0e220550",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.791626Z",
     "start_time": "2022-01-05T07:53:41.764701Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>recent</th>\n",
       "      <th>order_id</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>51220</td>\n",
       "      <td>107</td>\n",
       "      <td>1</td>\n",
       "      <td>129.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51221</td>\n",
       "      <td>234</td>\n",
       "      <td>1</td>\n",
       "      <td>29.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>51224</td>\n",
       "      <td>165</td>\n",
       "      <td>2</td>\n",
       "      <td>12.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51226</td>\n",
       "      <td>338</td>\n",
       "      <td>1</td>\n",
       "      <td>19.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51227</td>\n",
       "      <td>258</td>\n",
       "      <td>1</td>\n",
       "      <td>9.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  recent  order_id  amount\n",
       "0    51220     107         1   129.0\n",
       "1    51221     234         1    29.9\n",
       "2    51224     165         2    12.9\n",
       "3    51226     338         1    19.9\n",
       "4    51227     258         1     9.9"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "dc4af967",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.806587Z",
     "start_time": "2022-01-05T07:53:41.794621Z"
    }
   },
   "outputs": [],
   "source": [
    "rfm_df2 = rfm_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "35d7f149",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.837504Z",
     "start_time": "2022-01-05T07:53:41.809579Z"
    }
   },
   "outputs": [],
   "source": [
    "# np.linspace(0,1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "0c145514",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.883382Z",
     "start_time": "2022-01-05T07:53:41.840497Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0        0.5\n",
       "0.2       14.9\n",
       "0.4       61.9\n",
       "0.6      299.0\n",
       "0.8     2286.0\n",
       "1.0    89566.9\n",
       "Name: amount, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df2[\"amount\"].quantile(q=np.linspace(0,1,num=6),interpolation='nearest')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "661c4380",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "cef63489",
   "metadata": {},
   "source": [
    "#### 根据分位数分层"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "5423d792",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.915299Z",
     "start_time": "2022-01-05T07:53:41.887372Z"
    }
   },
   "outputs": [],
   "source": [
    "# 划分为 5 个等级\n",
    "recent_labels = [5,4,3,2,1]\n",
    "mount_labels = [1,2,3,4,5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "99fd5230",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.946213Z",
     "start_time": "2022-01-05T07:53:41.923275Z"
    }
   },
   "outputs": [],
   "source": [
    "m_bins = rfm_df2[\"amount\"].quantile(q=np.linspace(0,1,num=6),interpolation='nearest')\n",
    "r_bins = rfm_df2[\"recent\"].quantile(q=np.linspace(0,1,num=6),interpolation='nearest')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "c8551166",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:41.978128Z",
     "start_time": "2022-01-05T07:53:41.951200Z"
    }
   },
   "outputs": [],
   "source": [
    "rfm_df2[\"R\"] = pd.cut(rfm_df2[\"recent\"],bins=r_bins,labels=recent_labels,include_lowest=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "e2618c5e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.010043Z",
     "start_time": "2022-01-05T07:53:41.983116Z"
    }
   },
   "outputs": [],
   "source": [
    "rfm_df2[\"M\"] = pd.cut(rfm_df2[\"amount\"],bins=m_bins,labels=mount_labels,include_lowest=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "63ef9bda",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.040960Z",
     "start_time": "2022-01-05T07:53:42.014035Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>recent</th>\n",
       "      <th>order_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>R</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>51220</td>\n",
       "      <td>107</td>\n",
       "      <td>1</td>\n",
       "      <td>129.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51221</td>\n",
       "      <td>234</td>\n",
       "      <td>1</td>\n",
       "      <td>29.9</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>51224</td>\n",
       "      <td>165</td>\n",
       "      <td>2</td>\n",
       "      <td>12.9</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51226</td>\n",
       "      <td>338</td>\n",
       "      <td>1</td>\n",
       "      <td>19.9</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51227</td>\n",
       "      <td>258</td>\n",
       "      <td>1</td>\n",
       "      <td>9.9</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  recent  order_id  amount  R  M\n",
       "0    51220     107         1   129.0  4  3\n",
       "1    51221     234         1    29.9  2  2\n",
       "2    51224     165         2    12.9  3  1\n",
       "3    51226     338         1    19.9  1  2\n",
       "4    51227     258         1     9.9  2  1"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40c22d2c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "799643f7",
   "metadata": {},
   "source": [
    "#### 自定义分层"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "3ef32ea5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.071877Z",
     "start_time": "2022-01-05T07:53:42.044950Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1, 2, 3])"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.arange(1,4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "ae251443",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.103792Z",
     "start_time": "2022-01-05T07:53:42.075867Z"
    }
   },
   "outputs": [],
   "source": [
    "frequency_bins = [1,3,5,12]\n",
    "frequency_labels = [1,2,3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "4c101d30",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.136705Z",
     "start_time": "2022-01-05T07:53:42.107782Z"
    }
   },
   "outputs": [],
   "source": [
    "rfm_df2[\"F\"] = pd.cut(\n",
    "    rfm_df2[\"order_id\"]\n",
    "    ,bins=frequency_bins\n",
    "    ,labels=frequency_labels\n",
    "    ,include_lowest=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "9dd95148",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.183577Z",
     "start_time": "2022-01-05T07:53:42.139696Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>recent</th>\n",
       "      <th>order_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>R</th>\n",
       "      <th>M</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6682</th>\n",
       "      <td>64717</td>\n",
       "      <td>181</td>\n",
       "      <td>1</td>\n",
       "      <td>318.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18055</th>\n",
       "      <td>88118</td>\n",
       "      <td>312</td>\n",
       "      <td>1</td>\n",
       "      <td>11.9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4759</th>\n",
       "      <td>60823</td>\n",
       "      <td>252</td>\n",
       "      <td>1</td>\n",
       "      <td>79.0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5917</th>\n",
       "      <td>63134</td>\n",
       "      <td>295</td>\n",
       "      <td>2</td>\n",
       "      <td>2263.9</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8322</th>\n",
       "      <td>67946</td>\n",
       "      <td>212</td>\n",
       "      <td>1</td>\n",
       "      <td>8.9</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       user_id  recent  order_id  amount  R  M  F\n",
       "6682     64717     181         1   318.0  3  4  1\n",
       "18055    88118     312         1    11.9  1  1  1\n",
       "4759     60823     252         1    79.0  2  3  1\n",
       "5917     63134     295         2  2263.9  1  4  1\n",
       "8322     67946     212         1     8.9  2  1  1"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df2.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07ef0fc8",
   "metadata": {},
   "source": [
    "#### 打标签"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "c81fdc73",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.199538Z",
     "start_time": "2022-01-05T07:53:42.187570Z"
    }
   },
   "outputs": [],
   "source": [
    "rfm_df2 = rfm_df2.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "453fd9c5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.231450Z",
     "start_time": "2022-01-05T07:53:42.203526Z"
    }
   },
   "outputs": [],
   "source": [
    "rfm_df2[\"RFM\"] = rfm_df2[\"R\"]*3+rfm_df2[\"F\"]*2+rfm_df2[\"M\"]*5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "bf560c73",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.263367Z",
     "start_time": "2022-01-05T07:53:42.235441Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>recent</th>\n",
       "      <th>order_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>R</th>\n",
       "      <th>M</th>\n",
       "      <th>F</th>\n",
       "      <th>RFM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>36490</th>\n",
       "      <td>125851</td>\n",
       "      <td>205</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30893</th>\n",
       "      <td>114320</td>\n",
       "      <td>57</td>\n",
       "      <td>2</td>\n",
       "      <td>3408</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17864</th>\n",
       "      <td>87696</td>\n",
       "      <td>151</td>\n",
       "      <td>2</td>\n",
       "      <td>1924</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46014</th>\n",
       "      <td>145313</td>\n",
       "      <td>52</td>\n",
       "      <td>2</td>\n",
       "      <td>67</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38263</th>\n",
       "      <td>129396</td>\n",
       "      <td>132</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       user_id  recent  order_id  amount  R  M  F  RFM\n",
       "36490   125851     205         1       9  2  1  1   13\n",
       "30893   114320      57         2    3408  5  5  1   42\n",
       "17864    87696     151         2    1924  3  4  1   31\n",
       "46014   145313      52         2      67  5  3  1   32\n",
       "38263   129396     132         1      28  4  2  1   24"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "ce5f85a4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.295292Z",
     "start_time": "2022-01-05T07:53:42.266358Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>recent</th>\n",
       "      <th>order_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>R</th>\n",
       "      <th>M</th>\n",
       "      <th>F</th>\n",
       "      <th>RFM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>51220</td>\n",
       "      <td>107</td>\n",
       "      <td>1</td>\n",
       "      <td>129</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51221</td>\n",
       "      <td>234</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>51224</td>\n",
       "      <td>165</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51226</td>\n",
       "      <td>338</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51227</td>\n",
       "      <td>258</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55535</th>\n",
       "      <td>165092</td>\n",
       "      <td>194</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55536</th>\n",
       "      <td>165095</td>\n",
       "      <td>104</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55537</th>\n",
       "      <td>165096</td>\n",
       "      <td>347</td>\n",
       "      <td>1</td>\n",
       "      <td>499</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55538</th>\n",
       "      <td>165098</td>\n",
       "      <td>148</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55539</th>\n",
       "      <td>165100</td>\n",
       "      <td>311</td>\n",
       "      <td>1</td>\n",
       "      <td>6188</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>55540 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       user_id  recent  order_id  amount  R  M  F  RFM\n",
       "0        51220     107         1     129  4  3  1   29\n",
       "1        51221     234         1      29  2  2  1   18\n",
       "2        51224     165         2      12  3  1  1   16\n",
       "3        51226     338         1      19  1  2  1   15\n",
       "4        51227     258         1       9  2  1  1   13\n",
       "...        ...     ...       ...     ... .. .. ..  ...\n",
       "55535   165092     194         1       8  3  1  1   16\n",
       "55536   165095     104         1      16  4  2  1   24\n",
       "55537   165096     347         1     499  1  4  1   25\n",
       "55538   165098     148         1      10  3  1  1   16\n",
       "55539   165100     311         1    6188  1  5  1   30\n",
       "\n",
       "[55540 rows x 8 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e51ab92",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a57b9b0f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "abf90a48",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.327195Z",
     "start_time": "2022-01-05T07:53:42.299271Z"
    }
   },
   "outputs": [],
   "source": [
    "rfm_bins =rfm_df2[\"RFM\"].quantile(q=np.linspace(0,1,num=6),interpolation='nearest').unique()\n",
    "rfm_labels = ['流失客户','一般维持客户','重要挽留客户','重要唤回客户','重要价值客户']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "667bd3db",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.358113Z",
     "start_time": "2022-01-05T07:53:42.333179Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([10, 18, 23, 29, 34, 46])"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_bins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e2fb49f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "1e67bac0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.404985Z",
     "start_time": "2022-01-05T07:53:42.365094Z"
    }
   },
   "outputs": [],
   "source": [
    "rfm_df2[\"客户标签\"] = pd.cut(\n",
    "    rfm_df2[\"RFM\"],\n",
    "    bins=rfm_bins,\n",
    "    labels=rfm_labels,\n",
    "    include_lowest=True,\n",
    "    duplicates=\"drop\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "0f4cf8f1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.436901Z",
     "start_time": "2022-01-05T07:53:42.408977Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>recent</th>\n",
       "      <th>order_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>R</th>\n",
       "      <th>M</th>\n",
       "      <th>F</th>\n",
       "      <th>RFM</th>\n",
       "      <th>客户标签</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>51220</td>\n",
       "      <td>107</td>\n",
       "      <td>1</td>\n",
       "      <td>129</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>重要挽留客户</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51221</td>\n",
       "      <td>234</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>流失客户</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>51224</td>\n",
       "      <td>165</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>流失客户</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51226</td>\n",
       "      <td>338</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>流失客户</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51227</td>\n",
       "      <td>258</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>流失客户</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  recent  order_id  amount  R  M  F  RFM    客户标签\n",
       "0    51220     107         1     129  4  3  1   29  重要挽留客户\n",
       "1    51221     234         1      29  2  2  1   18    流失客户\n",
       "2    51224     165         2      12  3  1  1   16    流失客户\n",
       "3    51226     338         1      19  1  2  1   15    流失客户\n",
       "4    51227     258         1       9  2  1  1   13    流失客户"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "ef7a8f7a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.467820Z",
     "start_time": "2022-01-05T07:53:42.442885Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4    11228\n",
       "5    11164\n",
       "2    11110\n",
       "1    11079\n",
       "3    10959\n",
       "Name: R, dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df2[\"R\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "8df8638c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.515694Z",
     "start_time": "2022-01-05T07:53:42.470812Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    54974\n",
       "2      560\n",
       "3        6\n",
       "Name: F, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df2[\"F\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "566af14d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.547603Z",
     "start_time": "2022-01-05T07:53:42.519681Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3    11389\n",
       "1    11146\n",
       "2    11089\n",
       "5    11021\n",
       "4    10895\n",
       "Name: M, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df2[\"M\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "2d40f2f3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.579519Z",
     "start_time": "2022-01-05T07:53:42.551595Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "重要挽留客户    12576\n",
       "流失客户      12130\n",
       "一般维持客户    10556\n",
       "重要唤回客户    10455\n",
       "重要价值客户     9823\n",
       "Name: 客户标签, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df2[\"客户标签\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "b5c17169",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.611437Z",
     "start_time": "2022-01-05T07:53:42.585503Z"
    }
   },
   "outputs": [],
   "source": [
    "i = rfm_df2[\"客户标签\"].value_counts().index.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "2698fde5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.643349Z",
     "start_time": "2022-01-05T07:53:42.615426Z"
    }
   },
   "outputs": [],
   "source": [
    "v = rfm_df2[\"客户标签\"].value_counts().values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "242f7875",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.659306Z",
     "start_time": "2022-01-05T07:53:42.648338Z"
    }
   },
   "outputs": [],
   "source": [
    "# rfm_df2[rfm_df2[\"客户标签\"]==\"流失客户\"].sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "970c616b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "72877417",
   "metadata": {},
   "source": [
    "### 数据可视化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "ec99a910",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.907003Z",
     "start_time": "2022-01-05T07:53:42.663296Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "        <div id=\"90e9d187251142f695af6486c98c53ad\" style=\"width:900px; height:500px;\"></div>\n",
       "\n",
       "<script>\n",
       "        require(['echarts'], function(echarts) {\n",
       "                var chart_90e9d187251142f695af6486c98c53ad = echarts.init(\n",
       "                    document.getElementById('90e9d187251142f695af6486c98c53ad'), 'white', {renderer: 'canvas'});\n",
       "                var option_90e9d187251142f695af6486c98c53ad = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"pie\",\n",
       "            \"clockwise\": true,\n",
       "            \"data\": [\n",
       "                {\n",
       "                    \"name\": \"\\u91cd\\u8981\\u633d\\u7559\\u5ba2\\u6237\",\n",
       "                    \"value\": 12576\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u6d41\\u5931\\u5ba2\\u6237\",\n",
       "                    \"value\": 12130\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u4e00\\u822c\\u7ef4\\u6301\\u5ba2\\u6237\",\n",
       "                    \"value\": 10556\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u91cd\\u8981\\u5524\\u56de\\u5ba2\\u6237\",\n",
       "                    \"value\": 10455\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u91cd\\u8981\\u4ef7\\u503c\\u5ba2\\u6237\",\n",
       "                    \"value\": 9823\n",
       "                }\n",
       "            ],\n",
       "            \"radius\": [\n",
       "                \"30%\",\n",
       "                \"75%\"\n",
       "            ],\n",
       "            \"center\": [\n",
       "                \"50%\",\n",
       "                \"50%\"\n",
       "            ],\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8,\n",
       "                \"formatter\": \"{b}: {d}%\"\n",
       "            },\n",
       "            \"rippleEffect\": {\n",
       "                \"show\": true,\n",
       "                \"brushType\": \"stroke\",\n",
       "                \"scale\": 2.5,\n",
       "                \"period\": 4\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"\\u91cd\\u8981\\u633d\\u7559\\u5ba2\\u6237\",\n",
       "                \"\\u6d41\\u5931\\u5ba2\\u6237\",\n",
       "                \"\\u4e00\\u822c\\u7ef4\\u6301\\u5ba2\\u6237\",\n",
       "                \"\\u91cd\\u8981\\u5524\\u56de\\u5ba2\\u6237\",\n",
       "                \"\\u91cd\\u8981\\u4ef7\\u503c\\u5ba2\\u6237\"\n",
       "            ],\n",
       "            \"selected\": {},\n",
       "            \"show\": true,\n",
       "            \"left\": \"2%\",\n",
       "            \"top\": \"15%\",\n",
       "            \"orient\": \"vertical\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"itemWidth\": 25,\n",
       "            \"itemHeight\": 14\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"showContent\": true,\n",
       "        \"alwaysShowContent\": false,\n",
       "        \"showDelay\": 0,\n",
       "        \"hideDelay\": 100,\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0,\n",
       "        \"padding\": 5\n",
       "    },\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"\\u5ba2\\u6237\\u5206\\u5c42\\u5360\\u6bd4\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10\n",
       "        }\n",
       "    ]\n",
       "};\n",
       "                chart_90e9d187251142f695af6486c98c53ad.setOption(option_90e9d187251142f695af6486c98c53ad);\n",
       "        });\n",
       "    </script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x1e165bf2040>"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Pie\n",
    "from pyecharts.faker import Faker\n",
    "\n",
    "\n",
    "c = (\n",
    "    Pie()\n",
    "    .add(\n",
    "        \"\",\n",
    "        [list(z) for z in zip(i, v)],\n",
    "        radius=[\"30%\", \"75%\"],\n",
    "    )\n",
    "    .set_global_opts(\n",
    "        title_opts=opts.TitleOpts(title=\"客户分层占比\"),\n",
    "        legend_opts=opts.LegendOpts(orient=\"vertical\", pos_top=\"15%\", pos_left=\"2%\"),\n",
    "    )\n",
    "    .set_series_opts(label_opts=opts.LabelOpts(formatter=\"{b}: {d}%\"))\n",
    ")\n",
    "c.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "f8d852e8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-05T07:53:42.937921Z",
     "start_time": "2022-01-05T07:53:42.910991Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>客户标签</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>流失客户</td>\n",
       "      <td>228951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>一般维持客户</td>\n",
       "      <td>789369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>重要挽留客户</td>\n",
       "      <td>5285248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>重要唤回客户</td>\n",
       "      <td>19547844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>重要价值客户</td>\n",
       "      <td>33236897</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     客户标签    amount\n",
       "0    流失客户    228951\n",
       "1  一般维持客户    789369\n",
       "2  重要挽留客户   5285248\n",
       "3  重要唤回客户  19547844\n",
       "4  重要价值客户  33236897"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm_df2.groupby(by=\"客户标签\",as_index=False).agg({\"amount\":\"sum\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b694434",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b92c9c10",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cb6863a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e86aa0c7",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
